<?php

/**
 * eshare actions.
 *
 * @package    sf_sandbox
 * @subpackage eshare
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2692 2006-11-15 21:03:55Z fabien $
 */
class eshareActions extends sfActions
{

    public function executeDeleteDuplicate()
    {
        $query = "SELECT count(dist_id) as _count, dist_id
                        FROM mlm_account_ledger
                        WHERE remark = 'SHARE SPLIT'
                        group by dist_id ORDER BY _count DESC";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        while ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_count"] != null && $arr["_count"] > 1) {
                $dist_id = $arr["dist_id"];

                print_r("dist_id =" . $dist_id);
                print_r("<br>");

                $query = "SELECT account_id
                            FROM mlm_account_ledger
                            WHERE remark = 'SHARE SPLIT' AND dist_id =" . $dist_id . " ORDER BY account_id desc";

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset2 = $statement->executeQuery();

                if ($resultset2->next()) {
                    $arr2 = $resultset2->getRow();
                    print_r("deleted =" . $arr2['account_id']);
                    $tmp = MlmAccountLedgerPeer::retrieveByPK($arr2['account_id']);
                    $tmp->delete();
                }
            } else {
                break;
            }
        }
        $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_ESHARE, null);
        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    public function executeSplitShare()
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, 707, Criteria::GREATER_THAN);
        $mlmDistDBs = MlmDistributorPeer::doSelect($c);
        print_r("today =" . date("Y-m-d"));
        print_r("<br>");

        foreach ($mlmDistDBs as $mlmDistDB) {
            $distId = $mlmDistDB->getDistributorId();
            $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_dist_eshare_account
                    WHERE dist_id = " . $distId;

            $connection = Propel::getConnection();
            $statement = $connection->prepareStatement($query);
            $resultset = $statement->executeQuery();

            $paperShareQuantity = 0;
            if ($resultset->next()) {
                $arr = $resultset->getRow();
                if ($arr["SUB_TOTAL"] != null) {
                    $paperShareQuantity = $arr["SUB_TOTAL"];
                }
            }

            print_r($distId . "=" . $paperShareQuantity);
            print_r("<br>");
            if ($paperShareQuantity == 0)
                continue;

            $buyAmount = 0.12;

            $dateUtil = new DateUtil();
            $eshareValue = $buyAmount;
            $totalEshare = $paperShareQuantity * 2;

            $unitArrs = explode(",", Globals::ESHARE_SPLIT_SELL_UNIT);
            $dayArrs = explode(",", "36,51,66,81,97");

            $totalEshareConclude = 0;
            for ($i = 0; $i < count($unitArrs); $i++) {
                $totalEsharePartition = ceil($unitArrs[$i] / 100 * $totalEshare);
                $totalEshareConclude += $totalEsharePartition;
                print_r("splitted =" . $totalEsharePartition);
                print_r("<br>");

                $currentDate = $dateUtil->formatDate("Y-m-d", date("Y-m-d")) . " 00:00:00";
                $validSellDate = $dateUtil->addDate($currentDate, $dayArrs[$i], 0, 0);

                $mlm_dist_eshare_account = new MlmDistEshareAccount();
                $mlm_dist_eshare_account->setDistId($distId);
                $mlm_dist_eshare_account->setBuyPrice($eshareValue);
                $mlm_dist_eshare_account->setCredit($totalEsharePartition);
                $mlm_dist_eshare_account->setSellPrice(0);
                $mlm_dist_eshare_account->setDebit(0);
                $mlm_dist_eshare_account->setProfit(0);
                $mlm_dist_eshare_account->setShareBalance($totalEsharePartition);
                $mlm_dist_eshare_account->setRemark("SHARE SPLIT");
                $mlm_dist_eshare_account->setValidSellDate($validSellDate);
                //$mlm_dist_eshare_account->setSellDate("$y-$m-$d");
                $mlm_dist_eshare_account->setStatusCode(Globals::ESHARE_ACCOUNT_STATUS_ACTIVE);
                $mlm_dist_eshare_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_eshare_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_dist_eshare_account->save();
            }

            /* ******************************
           *  Update Account EShare
           ******************************* */
            $c = new Criteria();
            $c->add(MlmAccountPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ESHARE);
            $c->addAnd(MlmAccountPeer::DIST_ID, $distId);
            $mlmAccount = MlmAccountPeer::doSelectOne($c);
            $accountEshareBalance = $mlmAccount->getBalance();

            $mlm_account_ledger = new MlmAccountLedger();
            $mlm_account_ledger->setDistId($distId);
            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ESHARE);
            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY);
            $mlm_account_ledger->setRemark("SHARE SPLIT");
            $mlm_account_ledger->setCredit($totalEshareConclude);
            $mlm_account_ledger->setDebit(0);
            $mlm_account_ledger->setBalance($accountEshareBalance + $totalEshareConclude);
            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->save();

            $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_ESHARE);
        }

        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    public function executeRecontructTree()
    {
        if (sfConfig::get('sf_environment') == Globals::SF_ENVIRONMENT_DEV) {
            $distributor = MlmDistributorPeer::retrieveByPK(548);
            $distributor->setTreeLevel(26);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781|");
            $distributor->save();

            $distributor = MlmDistributorPeer::retrieveByPK(618);
            $distributor->setTreeLevel(27);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942|");
            $distributor->save();

            // 28
            $distributor = MlmDistributorPeer::retrieveByPK(617);
            $distributor->setTreeLevel(28);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||82482015|");
            $distributor->save();
            $distributor = MlmDistributorPeer::retrieveByPK(677);
            $distributor->setTreeLevel(28);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||98520797|");
            $distributor->save();

            // 29
            $distributor = MlmDistributorPeer::retrieveByPK(641);
            $distributor->setTreeLevel(29);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||82482015||39511909|");
            $distributor->save();

            $distributor = MlmDistributorPeer::retrieveByPK(642);
            $distributor->setTreeLevel(29);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||82482015||48734510|");
            $distributor->save();

            $distributor = MlmDistributorPeer::retrieveByPK(679);
            $distributor->setTreeLevel(29);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||98520797||36009577|");
            $distributor->save();

            // 30
            $distributor = MlmDistributorPeer::retrieveByPK(682);
            $distributor->setTreeLevel(30);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||98520797||36009577||51497944|");
            $distributor->save();
            $distributor = MlmDistributorPeer::retrieveByPK(687);
            $distributor->setTreeLevel(30);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||82482015||39511909||61783522|");
            $distributor->save();

            // 31
            $distributor = MlmDistributorPeer::retrieveByPK(686);
            $distributor->setTreeLevel(31);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||82482015||39511909||61783522||89411657|");
            $distributor->save();

            // 32
            $distributor = MlmDistributorPeer::retrieveByPK(689);
            $distributor->setTreeLevel(32);
            $distributor->setTreeStructure("|TV1||TV2||TV4||TV01||CNTV01||CNTV02||CNTV03||CNTV04||CNTV05||CNTV06||57596872||30263910||48819170||21283500||23505733||11792357||59341183||77286099||79181868||33997568||61149506||05696371||40558717||90737035||15789505||52629619||69441023||80957444||53071781||24252942||82482015||39511909||61783522||89411657||48924499|");
            $distributor->save();
        }
        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    public function executeResetSharePrice()
    {
        //if (sfConfig::get('sf_environment') == Globals::SF_ENVIRONMENT_DEV) {
        //            $distIds = "77286099";
        //            $distIds = "66114266";
        $distIds = "CNTV0001";
        //                        $distIds = "CNTV01,98611811,29301122,04885353,19788669,85080290,83987277,76858555,66114266,04688207,82461600,46934449,11875574";
        //              $distIds = "53071781,24252942,82482015,98520797,39511909,48734510,36009577,51497944,61783522,89411657,48924499";
        //            $distId018s = "35539712,13692915,34795460,75925460,16256025,56604251,21333291,71375525,51649433,26696787";
        //                        $allDistIds = "75728834,57596872";

        $distIdArrs = explode(",", $distIds);
        //                        $distIdArrs = explode(",", $distId018s);
        //                        $distIdArrs = explode(",", $allDistIds);
        $eshareValue = 0.18;
        for ($count = 0; $count < count($distIdArrs); $count++) {
            $c = new Criteria();
            //$c->add(MlmDistributorPeer::DISTRIBUTOR_CODE, $distIdArrs[$count], Criteria::EQUAL);
            $c->add(MlmDistributorPeer::TREE_STRUCTURE, "%" . $distIdArrs[$count] . "%", Criteria::LIKE);

            $distributors = MlmDistributorPeer::doSelect($c);
            foreach ($distributors as $distributor) {
                if ($distributor->getDistributorCode() == "75728834" || $distributor->getDistributorCode() == "57596872") {
                    print_r("*******************SKIP");
                    continue;
                }
                print_r($distributor->getDistributorCode());
                print_r("<br>");
                $packageDB = MlmPackagePeer::retrieveByPK($distributor->getRankId());
                $totalShareAmountNeeded = $packageDB->getPrice() * $packageDB->getShareRatio() / 100;

                $distId = $distributor->getDistributorId();

                /* *****************************************
               * Total Spend
               * ******************************************/
                $totalSpendBuyShare = 0;
                $totalMaintenanceSpendBuyShare = 0;

                $query = "SELECT SUM(debit) AS SUB_TOTAL FROM mlm_account_ledger
                            WHERE dist_id = " . $distId . "  AND remark = '' AND account_type = '" . Globals::ACCOUNT_TYPE_ECASH . "' AND transaction_type ='" . Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE . "'";

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset = $statement->executeQuery();


                if ($resultset->next()) {
                    $arr = $resultset->getRow();
                    if ($arr["SUB_TOTAL"] != null) {
                        $totalSpendBuyShare = $arr["SUB_TOTAL"];
                    }
                }

                $query = "SELECT SUM(debit) AS SUB_TOTAL FROM mlm_account_ledger
                            WHERE dist_id = " . $distId . "  AND remark = '' AND account_type = '" . Globals::ACCOUNT_TYPE_MAINTENANCE . "' AND transaction_type ='" . Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE . "'";

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset = $statement->executeQuery();

                if ($resultset->next()) {
                    $arr = $resultset->getRow();
                    if ($arr["SUB_TOTAL"] != null) {
                        $totalMaintenanceSpendBuyShare = $arr["SUB_TOTAL"];
                    }
                }

                /* *****************************************
               * delete eshare buying records
               * ******************************************/
                $query = "delete from mlm_account_ledger WHERE dist_id = " . $distId . " AND transaction_type ='" . Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE . "'";

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset = $statement->executeQuery();

                $query = "delete from mlm_account_ledger WHERE dist_id = " . $distId . " AND account_type ='" . Globals::ACCOUNT_TYPE_ESHARE . "'";

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset = $statement->executeQuery();

                $query = "delete from mlm_dist_eshare_account WHERE dist_id = " . $distId;

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset = $statement->executeQuery();

                $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_ESHARE, $distId);
                $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_ECASH, $distId);
                $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE, $distId);
                /* *****************************************
               * Start to buy eshare
               * ******************************************/
                $dateUtil = new DateUtil();
                if ($distributor->getDistributorId() < 900) {
                    //if ($distributor->getDistributorId() < 676) {
                    for ($actionIdx = 0; $actionIdx < 3; $actionIdx++) {
                        /* *****************************************
                       * retrieve ecash
                       * ******************************************/
                        $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_account_ledger
                                    WHERE dist_id = " . $distId . " AND account_type = '" . Globals::ACCOUNT_TYPE_ECASH . "'";

                        $connection = Propel::getConnection();
                        $statement = $connection->prepareStatement($query);
                        $resultset = $statement->executeQuery();

                        $ecash = 0;
                        if ($resultset->next()) {
                            $arr = $resultset->getRow();
                            if ($arr["SUB_TOTAL"] != null) {
                                $ecash = $arr["SUB_TOTAL"];
                            }
                        }

                        $totalEshare = 0;
                        $accountType = "";
                        $remark = "";
                        $buyAmount = 0;
                        if ($actionIdx == 0) {
                            // buy package eshare
                            $eshareValue = 0.18;
                            print_r("actionIdx=" . $actionIdx);
                            print_r("<br>");
                            print_r("totalShareAmountNeeded=" . $totalShareAmountNeeded);
                            print_r("<br>");
                            print_r("ecash=" . $ecash);
                            print_r("<br>");
                            //exit();
                            if ($totalShareAmountNeeded < $ecash) {
                                $totalEshare = $totalShareAmountNeeded / $eshareValue;
                                $buyAmount = $totalShareAmountNeeded;
                            } else {
                                $totalEshare = $ecash / $eshareValue;
                                $buyAmount = $ecash;
                            }
                            $accountType = Globals::ACCOUNT_TYPE_ECASH;
                            $remark = "SHARE OFFER";
                        } else if ($actionIdx == 1) {
                            print_r("actionIdx=" . $actionIdx);
                            print_r("<br>");
                            // buy eshare by ecash
                            $query = "SELECT SUM(debit) AS SUB_TOTAL FROM mlm_account_ledger
                                        WHERE account_type = '" . Globals::ACCOUNT_TYPE_ECASH . "' AND transaction_type ='" . Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE . "'";

                            $connection = Propel::getConnection();
                            $statement = $connection->prepareStatement($query);
                            $resultset = $statement->executeQuery();

                            $totalShareSoldValue = 0;
                            if ($resultset->next()) {
                                $arr = $resultset->getRow();
                                if ($arr["SUB_TOTAL"] != null) {
                                    $totalShareSoldValue = $arr["SUB_TOTAL"];
                                }
                            }

                            $c = new Criteria();
                            $c->add(MlmEsharePriceSettingPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
                            $c->add(MlmEsharePriceSettingPeer::VOLUME, $totalShareSoldValue, Criteria::GREATER_THAN);
                            $c->addAscendingOrderByColumn(MlmEsharePriceSettingPeer::VOLUME);
                            $esharePriceSettingDB = MlmEsharePriceSettingPeer::doSelectOne($c);
                            $eshareValue = $esharePriceSettingDB->getShareValue();

                            if ($totalSpendBuyShare < $ecash) {
                                $totalEshare = $totalSpendBuyShare / $eshareValue;
                                $buyAmount = $totalShareAmountNeeded;
                            } else {
                                $totalEshare = $ecash / $eshareValue;
                                $buyAmount = $ecash;
                            }

                            $accountType = Globals::ACCOUNT_TYPE_ECASH;
                            $remark = "SHARE OFFER (ECASH)";
                        } else if ($actionIdx == 2) {
                            print_r("actionIdx=" . $actionIdx);
                            print_r("<br>");
                            // buy eshare by maintenance
                            $query = "SELECT SUM(debit) AS SUB_TOTAL FROM mlm_account_ledger
                                        WHERE account_type = '" . Globals::ACCOUNT_TYPE_ECASH . "' AND transaction_type ='" . Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE . "'";

                            $connection = Propel::getConnection();
                            $statement = $connection->prepareStatement($query);
                            $resultset = $statement->executeQuery();

                            $totalShareSoldValue = 0;
                            if ($resultset->next()) {
                                $arr = $resultset->getRow();
                                if ($arr["SUB_TOTAL"] != null) {
                                    $totalShareSoldValue = $arr["SUB_TOTAL"];
                                }
                            }

                            $c = new Criteria();
                            $c->add(MlmEsharePriceSettingPeer::STATUS_CODE, Globals::STATUS_ACTIVE);
                            $c->add(MlmEsharePriceSettingPeer::VOLUME, $totalShareSoldValue, Criteria::GREATER_THAN);
                            $c->addAscendingOrderByColumn(MlmEsharePriceSettingPeer::VOLUME);
                            $esharePriceSettingDB = MlmEsharePriceSettingPeer::doSelectOne($c);
                            $eshareValue = $esharePriceSettingDB->getShareValue();

                            if ($totalMaintenanceSpendBuyShare < $ecash) {
                                $totalEshare = $totalMaintenanceSpendBuyShare / $eshareValue;
                                $buyAmount = $totalShareAmountNeeded;
                            } else {
                                $totalEshare = $ecash / $eshareValue;
                                $buyAmount = $ecash;
                            }
                            $accountType = Globals::ACCOUNT_TYPE_MAINTENANCE;
                            $remark = "SHARE OFFER (MAINTENANCE)";
                        }

                        if ($totalEshare > 0) {
                            $unitArrs = explode(",", Globals::ESHARE_SPLIT_SELL_UNIT);
                            $dayArrs = explode(",", Globals::ESHARE_SPLIT_SELL_DAY);

                            $totalEshareConclude = 0;
                            for ($i = 0; $i < count($unitArrs); $i++) {
                                $totalEsharePartition = ceil($unitArrs[$i] / 100 * $totalEshare);
                                $totalEshareConclude += $totalEsharePartition;

                                $currentDate = $dateUtil->formatDate("Y-m-d", date("Y-m-d")) . " 00:00:00";
                                $validSellDate = $dateUtil->addDate($currentDate, $dayArrs[$i], 0, 0);

                                $mlm_dist_eshare_account = new MlmDistEshareAccount();
                                $mlm_dist_eshare_account->setDistId($distId);
                                $mlm_dist_eshare_account->setBuyPrice($eshareValue);
                                $mlm_dist_eshare_account->setCredit($totalEsharePartition);
                                $mlm_dist_eshare_account->setSellPrice(0);
                                $mlm_dist_eshare_account->setDebit(0);
                                $mlm_dist_eshare_account->setProfit(0);
                                $mlm_dist_eshare_account->setShareBalance($totalEsharePartition);
                                $mlm_dist_eshare_account->setRemark($remark);
                                $mlm_dist_eshare_account->setValidSellDate($validSellDate);
                                $mlm_dist_eshare_account->setStatusCode(Globals::ESHARE_ACCOUNT_STATUS_ACTIVE);
                                $mlm_dist_eshare_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlm_dist_eshare_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                                $mlm_dist_eshare_account->save();
                            }

                            /* ******************************
                           *  Update Account Ecash
                           ******************************* */
                            $accountEcashBalance = 0;
                            $c = new Criteria();
                            $c->add(MlmAccountLedgerPeer::DIST_ID, $distId);
                            $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, $accountType);
                            $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
                            $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);

                            if ($accountLedgerDB) {
                                $accountEcashBalance = $accountLedgerDB->getBalance();
                            }

                            $mlm_account_ledger = new MlmAccountLedger();
                            $mlm_account_ledger->setAccountType($accountType);
                            $mlm_account_ledger->setDistId($distId);
                            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE);
                            $mlm_account_ledger->setRemark($remark);
                            $mlm_account_ledger->setCredit(0);
                            $mlm_account_ledger->setDebit($buyAmount);
                            $mlm_account_ledger->setBalance($accountEcashBalance - $buyAmount);
                            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->save();

                            /* ******************************
                           *  Update Account EShare
                           ******************************* */
                            $accountEshareBalance = 0;
                            $c = new Criteria();
                            $c->add(MlmAccountLedgerPeer::DIST_ID, $distId);
                            $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ESHARE);
                            $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
                            $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);

                            if ($accountLedgerDB) {
                                $accountEshareBalance = $accountLedgerDB->getBalance();
                            }

                            $mlm_account_ledger = new MlmAccountLedger();
                            $mlm_account_ledger->setDistId($distId);
                            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ESHARE);
                            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY);
                            $mlm_account_ledger->setRemark($remark);
                            $mlm_account_ledger->setCredit($totalEshareConclude);
                            $mlm_account_ledger->setDebit(0);
                            $mlm_account_ledger->setBalance($accountEshareBalance + $totalEshareConclude);
                            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                            $mlm_account_ledger->save();

                        }
                    }
                }
                $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_ECASH);
                $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_MAINTENANCE);
                $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_ESHARE);
            }
        }
        //}
        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    public function executeInitData()
    {
        $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_ESHARE, 1);
        $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_ECASH, 1);
        $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE, 1);

        $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_ESHARE, 81);
        $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_ECASH, 81);
        $this->revalidateByAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE, 81);
        print_r("Done");
        return sfView::HEADER_ONLY;
    }

    function revalidateByAccountType($accountType, $distId)
    {
        print_r("accountType" . $accountType);
        $c = new Criteria();
        if ($distId != null) {
            $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $distId);
        }
        $distributors = MlmDistributorPeer::doSelect($c);
        foreach ($distributors as $distributor) {
            print_r("DIST ID" . $distributor->getDistributorId());
            print_r("<br>");

            $c = new Criteria();
            $c->add(MlmAccountLedgerPeer::DIST_ID, $distributor->getDistributorId());
            $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, $accountType);
            $c->addAscendingOrderByColumn(MlmAccountLedgerPeer::ACCOUNT_ID);
            $accountLedgers = MlmAccountLedgerPeer::doSelect($c);

            $balance = 0;
            print_r("<br>");
            foreach ($accountLedgers as $accountLedger) {
                $balance = $balance + $accountLedger->getCredit() - $accountLedger->getDebit();
                $accountLedger->setBalance($balance);
                $accountLedger->save();
                print_r($accountType . " balance=" . $balance);
                print_r("<br>");
            }

            $this->revalidateAccount($distributor->getDistributorId(), $accountType);
        }
    }

    public function executeIndex()
    {
        print_r(date('Y-m-d H:i:s'));
        print_r("<br>");
        print_r(date('Y-m-d h:i:s A'));
        print_r("<br>");
        print_r(date('d'));
        print_r(date('H'));
        print_r("<br>");
        print_r(date('Ym'));


        $todayDateHour = 19;
        if ($todayDateHour >= 20 && $todayDateHour <= 23) {
            var_dump("yes");
        } else {
            var_dump("no");
        }
        $dateUtil = new DateUtil();

        $c = new Criteria();
        $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, 563, Criteria::EQUAL);
        $c->add(MlmDistributorPeer::CREATED_ON, "2012-04-14 18:00:00", Criteria::LESS_EQUAL);
        $totalRecords = MlmDistributorPeer::doCount($c);
        var_dump($totalRecords);

        if ($totalRecords > 0) {
            var_dump("valid");
        } else {
            var_dump("Invalid");
        }
        //        return $this->redirect('/eshare/tradingCenter');
    }

    public function executeReinvestEshare()
    {
        $maintenance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);
        if ($maintenance < Globals::REINVEST_AMOUNT) {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Reinvest CPS required minimum ".Globals::REINVEST_AMOUNT." in Maintenance Account."));
            return $this->redirect('/eshare/tradingCenter');
        }
        /* *****************************************
         * Setting
         * ******************************************/
        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_SHARE_MARKET);
        $settingDB = AppSettingPeer::doSelectOne($c);

        $validToSellShare = false;
        $validToBuyShare = false;
        if ($settingDB->getSettingValue() == '0') {
            $todayDateDay = date('d');
            $todayDateHour = date('H');
            $todayDateYearMonth = date('Ymd');

            if ($todayDateYearMonth == "20120415") {
                //special case
                if (($todayDateDay == "15" || $todayDateDay == "30") && $todayDateHour >= 20 && $todayDateHour <= 23) {
                    $c = new Criteria();
                    $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID), Criteria::EQUAL);
                    $c->add(MlmDistributorPeer::CREATED_ON, "2012-04-14 18:00:00", Criteria::LESS_EQUAL);
                    $totalRecords = MlmDistributorPeer::doCount($c);

                    if ($totalRecords > 0) {
                        $validToSellShare = true;
                    }
                }
                if (($todayDateDay == "15" || $todayDateDay == "30") && $todayDateHour >= 18 && $todayDateHour <= 19) {
                    $validToBuyShare = true;
                }
            } else {
                // normal case
                if (($todayDateDay == "15" || $todayDateDay == "30")) {
                    $validToSellShare = true;
                }
                $validToBuyShare = true;
            }
        }
        $this->validToSellShare = $validToSellShare;
        $this->validToBuyShare = $validToBuyShare;
        $this->maintenance = $maintenance;

        $eshareTradingService = new EShareTradingService();
        $this->eshareTradingDto = $eshareTradingService->fetchAllShareTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));
    }

    public function executeTradingCenter()
    {
        /* *****************************************
         * Setting
         * ******************************************/
        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_SHARE_MARKET);
        $settingDB = AppSettingPeer::doSelectOne($c);

        $validToSellShare = false;
        $validToBuyShare = false;
        if ($settingDB->getSettingValue() == '0') {
            $todayDateDay = date('d');
            $todayDateHour = date('H');
            $todayDateYearMonth = date('Ymd');

            if ($todayDateYearMonth == "20120415") {
                //special case
                if (($todayDateDay == "15" || $todayDateDay == "30") && $todayDateHour >= 20 && $todayDateHour <= 23) {
                    $c = new Criteria();
                    $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID), Criteria::EQUAL);
                    $c->add(MlmDistributorPeer::CREATED_ON, "2012-04-14 18:00:00", Criteria::LESS_EQUAL);
                    $totalRecords = MlmDistributorPeer::doCount($c);

                    if ($totalRecords > 0) {
                        $validToSellShare = true;
                    }
                }
                if (($todayDateDay == "15" || $todayDateDay == "30") && $todayDateHour >= 18 && $todayDateHour <= 19) {
                    $validToBuyShare = true;
                }
            } else {
                // normal case
                //                if (($todayDateDay == "15" || $todayDateDay == "30" || ($this->getUser()->getAttribute(Globals::SESSION_DISTID) == 30 && $todayDateDay == "16"))) {
                if (($todayDateDay == "5" || $todayDateDay == "15" || $todayDateDay == "25") && $todayDateHour >= 8 && $todayDateHour < 17) {
                    $validToSellShare = true;
                }
                $validToBuyShare = true;
            }
        }
        if ($this->getUser()->getAttribute(Globals::SESSION_DISTID) == 1) {
            $validToSellShare = true;
        }
        $this->validToSellShare = $validToSellShare;
        $this->validToBuyShare = $validToBuyShare;

        $eshareTradingService = new EShareTradingService();
        $this->eshareTradingDto = $eshareTradingService->fetchAllShareTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));

        /* *****************************************
         * fetch last 14 days share price log
         * ******************************************/
        $dateUtil = new DateUtil();
        $date = $dateUtil->formatDate("Y-m-d", date("Y-m-d"));
        $query = "SELECT log_id, share_value, created_by, created_on, updated_by, updated_on
	                    FROM mlm_eshare_log WHERE created_on <= '" . $date . " 23:59:59' order by created_on desc limit 30";
        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $arr = array();
        $i = 0;
        while ($resultset->next()) {
            $arr[$i++] = $resultset->getRow();
        }
        $this->eshareLogs = $arr;
    }

    public function executeSellCps()
    {
        /* *****************************************
         * Setting
         * ******************************************/
        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_SHARE_MARKET);
        $settingDB = AppSettingPeer::doSelectOne($c);

        $validToSellShare = false;
        $validToBuyShare = false;
        if ($settingDB->getSettingValue() == '0') {
            $todayDateDay = date('d');
            $todayDateHour = date('H');
            $todayDateYearMonth = date('Ymd');

            if ($todayDateYearMonth == "20120415") {
                //special case
                if (($todayDateDay == "15" || $todayDateDay == "30") && $todayDateHour >= 20 && $todayDateHour <= 23) {
                    $c = new Criteria();
                    $c->add(MlmDistributorPeer::DISTRIBUTOR_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID), Criteria::EQUAL);
                    $c->add(MlmDistributorPeer::CREATED_ON, "2012-04-14 18:00:00", Criteria::LESS_EQUAL);
                    $totalRecords = MlmDistributorPeer::doCount($c);

                    if ($totalRecords > 0) {
                        $validToSellShare = true;
                    }
                }
                if (($todayDateDay == "15" || $todayDateDay == "30") && $todayDateHour >= 18 && $todayDateHour <= 19) {
                    $validToBuyShare = true;
                }
            } else {
                // normal case
                //                if (($todayDateDay == "15" || $todayDateDay == "30" || ($this->getUser()->getAttribute(Globals::SESSION_DISTID) == 30 && $todayDateDay == "16"))) {
                if (($todayDateDay == "5" || $todayDateDay == "15" || $todayDateDay == "25") && $todayDateHour >= 8 && $todayDateHour < 17) {
                    $validToSellShare = true;
                }
                $validToBuyShare = true;
            }
        }
        if ($this->getUser()->getAttribute(Globals::SESSION_DISTID) == 1) {
            $validToSellShare = true;
        }
        $this->validToSellShare = $validToSellShare;
        $this->validToBuyShare = $validToBuyShare;

        $eshareTradingService = new EShareTradingService();
        $this->eshareTradingDto = $eshareTradingService->fetchAllShareTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));

        /* *****************************************
         * fetch last 14 days share price log
         * ******************************************/
        $dateUtil = new DateUtil();
        $date = $dateUtil->formatDate("Y-m-d", date("Y-m-d"));
        $query = "SELECT log_id, share_value, created_by, created_on, updated_by, updated_on
	                    FROM mlm_eshare_log WHERE created_on <= '" . $date . " 23:59:59' order by created_on desc limit 30";
        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $arr = array();
        $i = 0;
        while ($resultset->next()) {
            $arr[$i++] = $resultset->getRow();
        }
        $this->eshareLogs = $arr;
    }

    public function executeSellShare()
    {
        $shareId = $this->getRequestParameter('shareId');
        $currentDate = date("Y-m-d");

        $mlm_dist_eshare_account = MlmDistEshareAccountPeer::retrieveByPK($shareId);
        $this->forward404Unless($mlm_dist_eshare_account);

        Globals::SESSION_DISTID; // this is neccessary to initialial Globals class
        $eshareTradingService = new EShareTradingService();
        $eshareValue = $eshareTradingService->fetchEsharePrice();

        $buyPrice = $mlm_dist_eshare_account->getBuyPrice();
        $credit = $mlm_dist_eshare_account->getCredit();
        $sellPrice = $eshareValue;
        $debit = $credit;
        $profit = $credit * ($sellPrice - $buyPrice);

        $mlm_dist_eshare_account->setSellPrice($sellPrice);
        $mlm_dist_eshare_account->setDebit($debit);
        $mlm_dist_eshare_account->setProfit($profit);
        $mlm_dist_eshare_account->setShareBalance(0);
        //$mlm_dist_eshare_account->setRemark("");
        $mlm_dist_eshare_account->setSellDate($currentDate);
        $mlm_dist_eshare_account->setStatusCode(Globals::ESHARE_ACCOUNT_STATUS_COMPLETE);
        $mlm_dist_eshare_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_dist_eshare_account->save();

        $distId = $mlm_dist_eshare_account->getDistId();

        /* ******************************
         *  Update Account EShare
         ******************************* */
        $c = new Criteria();
        $c->add(MlmAccountPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ESHARE);
        $c->addAnd(MlmAccountPeer::DIST_ID, $distId);
        $mlmAccount = MlmAccountPeer::doSelectOne($c);
        $accountEshareBalance = $mlmAccount->getBalance();

        $mlm_account_ledger = new MlmAccountLedger();
        $mlm_account_ledger->setDistId($distId);
        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ESHARE);
        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL);
        $mlm_account_ledger->setRemark("");
        $mlm_account_ledger->setCredit(0);
        $mlm_account_ledger->setDebit($debit);
        $mlm_account_ledger->setBalance($accountEshareBalance - $debit);
        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->save();

        $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_ESHARE);

        /* **********************************
         *  e-cash
         ************************************/
        $ecashAfterSell = $credit * $sellPrice;
        $maintenanceBalance = $ecashAfterSell * Globals::BONUS_MAINTENANCE_PERCENTAGE;

        $c = new Criteria();
        $c->add(MlmAccountLedgerPeer::DIST_ID, $distId);
        $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ECASH);
        $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
        $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
        $this->forward404Unless($accountLedgerDB);

        $distAccountECashBalance = $accountLedgerDB->getBalance();

        $mlm_account_ledger = new MlmAccountLedger();
        $mlm_account_ledger->setDistId($distId);
        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL_ESHARE);
        $mlm_account_ledger->setRemark("SELL ESHARE VOLUME:" . number_format($credit, 2) . ", VALUE:" . $sellPrice . " (" . $currentDate . ")");
        $mlm_account_ledger->setCredit($ecashAfterSell);
        $mlm_account_ledger->setDebit(0);
        $mlm_account_ledger->setBalance($distAccountECashBalance + $ecashAfterSell);
        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->save();

        $maintenanceEcashAccountLedger = new MlmAccountLedger();
        $maintenanceEcashAccountLedger->setDistId($distId);
        $maintenanceEcashAccountLedger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
        $maintenanceEcashAccountLedger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);
        $maintenanceEcashAccountLedger->setRemark("MAINTENANCE BALANCE (" . number_format($ecashAfterSell, 2) . ")");
        $maintenanceEcashAccountLedger->setCredit(0);
        $maintenanceEcashAccountLedger->setDebit($maintenanceBalance);
        $maintenanceEcashAccountLedger->setBalance($distAccountECashBalance + $ecashAfterSell - $maintenanceBalance);
        $maintenanceEcashAccountLedger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceEcashAccountLedger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceEcashAccountLedger->save();

        $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_ECASH);

        /******************************/
        /*  Maintenance
        /******************************/
        $c = new Criteria();
        $c->add(MlmAccountLedgerPeer::DIST_ID, $distId);
        $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_MAINTENANCE);
        $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
        $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
        $this->forward404Unless($accountLedgerDB);
        $distAccountMaintenanceBalance = $accountLedgerDB->getBalance();

        $maintenanceAccountLedger = new MlmAccountLedger();
        $maintenanceAccountLedger->setDistId($distId);
        $maintenanceAccountLedger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
        $maintenanceAccountLedger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL_ESHARE);
        $maintenanceAccountLedger->setRemark("SELL ESHARE VOLUME:" . number_format($credit, 2) . ", VALUE:" . $sellPrice . " (" . $currentDate . ")");
        $maintenanceAccountLedger->setCredit($maintenanceBalance);
        $maintenanceAccountLedger->setDebit(0);
        $maintenanceAccountLedger->setBalance($distAccountMaintenanceBalance + $maintenanceBalance);
        $maintenanceAccountLedger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceAccountLedger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceAccountLedger->save();

        $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_MAINTENANCE);

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Sell share successfully."));
        return $this->redirect('/eshare/tradingCenter');
    }

    public function executeCancelSellShareToWaitingList()
    {
        $shareId = $this->getRequestParameter('shareId');

        $c = new Criteria();
        $c->add(MlmEshareSellWaitingListPeer::SELL_ID, $shareId);
        $c->add(MlmEshareSellWaitingListPeer::STATUS_CODE, Globals::ESHARE_ACCOUNT_STATUS_ACTIVE);
        $mlm_eshare_sell_waiting_list = MlmEshareSellWaitingListPeer::doSelectOne($c);

        $this->forward404Unless($mlm_eshare_sell_waiting_list);

        $mlm_eshare_sell_waiting_list->setStatusCode(Globals::ESHARE_WAITING_STATUS_CANCEL);
        $mlm_eshare_sell_waiting_list->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_eshare_sell_waiting_list->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_eshare_sell_waiting_list->save();

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Cancel sell CPS successfully."));
        return $this->redirect('/eshare/sellCps');
    }

    public function executeSellShareToWaitingList()
    {
        $sellQuantity = $this->getRequestParameter('sellQuantity');
        $sellPrice = $this->getRequestParameter('sellPrice');

        $totalPaperShare = $this->getPaperShareQuantity($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $totalPaperShareInWaitingList = $this->getPaperShareQuantityInWaitingList($this->getUser()->getAttribute(Globals::SESSION_DISTID));

        if ($sellQuantity == 0) {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("CPS Quantity cannot be zero."));
            return $this->redirect('/eshare/sellCps');
        }
        if (($sellQuantity + $totalPaperShareInWaitingList) > $totalPaperShare) {
            $this->setFlash('errorMsg', $this->getContext()->getI18N()->__("Insufficient CPS Quantity."));
            return $this->redirect('/eshare/sellCps');
        }

        $con = Propel::getConnection(MlmEcashWithdrawPeer::DATABASE_NAME);
        try {
            $con->begin();

            $mlm_eshare_sell_waiting_list = new MlmEshareSellWaitingList();
            $mlm_eshare_sell_waiting_list->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_eshare_sell_waiting_list->setPrice($sellPrice);
            $mlm_eshare_sell_waiting_list->setQty($sellQuantity);
            $mlm_eshare_sell_waiting_list->setMatchBalance(0);
            $mlm_eshare_sell_waiting_list->setStatusCode(Globals::ESHARE_WAITING_STATUS_ACTIVE);
            $mlm_eshare_sell_waiting_list->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_eshare_sell_waiting_list->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_eshare_sell_waiting_list->save();

            $con->commit();

            $this->setFlash('successMsg', $this->getContext()->getI18N()->__("CPS pending to sell in waiting list now."));
        } catch (PropelException $e) {
            $con->rollback();
            throw $e;
        }

        return $this->redirect('/eshare/sellCps');
    }

    public function executeBuyShare()
    {
        $buyAmount = $this->getRequestParameter('txtBuyAmount');
        $maintenanceTransaction = false;

        if ($this->getRequestParameter('buyType') && $this->getRequestParameter('buyType') == "maintenance") {
            $maintenanceTransaction = true;
        }

        $eshareTradingService = new EShareTradingService();
        $eshareTradingDto = $eshareTradingService->fetchAllShareTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $ecash = $eshareTradingDto->getEcash();

        if ($maintenanceTransaction == true) {
            $ecash = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);
        }

        if ($ecash == 0) {
            $this->setFlash('successMsg', "Buy amount must be greater than 0.");

            if ($maintenanceTransaction == true) {
                return $this->redirect('/eshare/reinvestEshare');
            } else {
                return $this->redirect('/eshare/tradingCenter');
            }
        }
        if ($ecash < $buyAmount) {
            $this->setFlash('successMsg', "In-sufficient e-Cash.");

            if ($maintenanceTransaction == true) {
                return $this->redirect('/eshare/reinvestEshare');
            } else {
                return $this->redirect('/eshare/tradingCenter');
            }
        }

        $dateUtil = new DateUtil();
        $eshareValue = $eshareTradingDto->getEsharePrice();
        $totalEshare = floor($buyAmount / $eshareValue);

        $unitArrs = explode(",", Globals::ESHARE_SPLIT_SELL_UNIT);
        $dayArrs = explode(",", Globals::ESHARE_SPLIT_SELL_DAY);

        /*$totalEshareConclude = 0;
        for ($i = 0; $i < count($unitArrs); $i++) {
            if ($totalEshareConclude >= $totalEshare) {
                break;
            }
            $totalEsharePartition = ceil($unitArrs[$i] / 100 * $totalEshare);
            $totalEshareConclude += $totalEsharePartition;

            if ($totalEshareConclude > $totalEshare) {
                $totalEsharePartition = $totalEsharePartition - ($totalEshareConclude - $totalEshare);

                if ($totalEsharePartition == 0) {
                    break;
                }
            }*/

        //$currentDate = $dateUtil->formatDate("Y-m-d", date("Y-m-d")) . " 00:00:00";
        //$validSellDate = $dateUtil->addDate($currentDate, $dayArrs[$i], 0, 0);

        $mlm_dist_eshare_account = new MlmDistEshareAccount();
        $mlm_dist_eshare_account->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $mlm_dist_eshare_account->setBuyPrice($eshareValue);
        $mlm_dist_eshare_account->setCredit($totalEshare);
        //$mlm_dist_eshare_account->setCredit($totalEsharePartition);
        $mlm_dist_eshare_account->setSellPrice(0);
        $mlm_dist_eshare_account->setDebit(0);
        $mlm_dist_eshare_account->setProfit(0);
        $mlm_dist_eshare_account->setShareBalance($totalEshare);
        //$mlm_dist_eshare_account->setShareBalance($totalEsharePartition);
        $mlm_dist_eshare_account->setRemark("");
        //$mlm_dist_eshare_account->setValidSellDate($validSellDate);
        $mlm_dist_eshare_account->setStatusCode(Globals::ESHARE_ACCOUNT_STATUS_ACTIVE);
        $mlm_dist_eshare_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_dist_eshare_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_dist_eshare_account->save();
        //}

        /* ******************************
         *  Update Account Ecash
         ******************************* */
        $c = new Criteria();
        $accountEcashBalance = 0;
        if ($maintenanceTransaction == true) {
            $accountEcashBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MAINTENANCE);
        } else {
            $accountEcashBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
        }

        $mlm_account_ledger = new MlmAccountLedger();
        if ($maintenanceTransaction == true) {
            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
        } else {
            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
        }
        $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE);
        $mlm_account_ledger->setRemark("");
        $mlm_account_ledger->setCredit(0);
        $mlm_account_ledger->setDebit($buyAmount);
        $mlm_account_ledger->setBalance($accountEcashBalance - $buyAmount);
        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->save();

        if ($maintenanceTransaction == true) {
            $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MAINTENANCE);
        } else {
            $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
        }

        /* ******************************
         *  Update Account EShare
         ******************************* */
        $accountEshareBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ESHARE);

        $mlm_account_ledger = new MlmAccountLedger();
        $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ESHARE);
        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY);
        $mlm_account_ledger->setRemark("");
        $mlm_account_ledger->setCredit($totalEshare);
        $mlm_account_ledger->setDebit(0);
        $mlm_account_ledger->setBalance($accountEshareBalance + $totalEshare);
        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->save();

        $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ESHARE);

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Buy share successfully."));

        if ($maintenanceTransaction == true) {
            return $this->redirect('/eshare/reinvestEshare');
        } else {
            return $this->redirect('/eshare/tradingCenter');
        }
    }

    public function executeBuyShareFromWaitingList()
    {
        $buyId = $this->getRequestParameter('buyId');
        $totalEshare = $this->getRequestParameter('buyQuantity');
        $maintenanceTransaction = false;

        if ($this->getRequestParameter('buyType') && $this->getRequestParameter('buyType') == "maintenance") {
            $maintenanceTransaction = true;
        }

        $eshareTradingService = new EShareTradingService();
        $eshareTradingDto = $eshareTradingService->fetchAllShareTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $ecash = $eshareTradingDto->getEcash();

        if ($maintenanceTransaction == true) {
            $ecash = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);
        }

        if ($totalEshare == 0) {
            $this->setFlash('errorMsg', "Buy amount must be greater than 0.");

            if ($maintenanceTransaction == true) {
                return $this->redirect('/eshare/reinvestEshare');
            } else {
                return $this->redirect('/eshare/tradingCenter');
            }
        }
        $con = Propel::getConnection(MlmEcashWithdrawPeer::DATABASE_NAME);
        try {
            $con->begin();
            /*******************************/
            /**** WAITING LIST *************/
            /*******************************/
            $array = explode(',', Globals::ESHARE_WAITING_STATUS_ACTIVE . "," . Globals::ESHARE_WAITING_STATUS_PARTIAL_MATCH);

            $c = new Criteria();
            $c->add(MlmEshareSellWaitingListPeer::SELL_ID, $buyId);
            $c->add(MlmEshareSellWaitingListPeer::STATUS_CODE, $array, Criteria::IN);
            $mlm_eshare_sell_waiting_list = MlmEshareSellWaitingListPeer::doSelectOne($c);

            $this->forward404Unless($mlm_eshare_sell_waiting_list);

            $buyAmount = $totalEshare * $mlm_eshare_sell_waiting_list->getPrice();
            if ($ecash < $buyAmount) {
                $this->setFlash('errorMsg', "In-sufficient e-Cash.");

                if ($maintenanceTransaction == true) {
                    return $this->redirect('/eshare/reinvestEshare');
                } else {
                    return $this->redirect('/eshare/tradingCenter');
                }
            }
            $waitingEshareRemaining = $mlm_eshare_sell_waiting_list->getQty() - $mlm_eshare_sell_waiting_list->getMatchBalance();
            if ($waitingEshareRemaining < $totalEshare) {
                $this->setFlash('errorMsg', "In-sufficient CPS Balance.");

                if ($maintenanceTransaction == true) {
                    return $this->redirect('/eshare/reinvestEshare');
                } else {
                    return $this->redirect('/eshare/tradingCenter');
                }
            }
            $mlm_dist_eshare_account = new MlmDistEshareAccount();
            $mlm_dist_eshare_account->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_dist_eshare_account->setBuyPrice($mlm_eshare_sell_waiting_list->getPrice());
            $mlm_dist_eshare_account->setCredit($totalEshare);
            //$mlm_dist_eshare_account->setCredit($totalEsharePartition);
            $mlm_dist_eshare_account->setSellPrice(0);
            $mlm_dist_eshare_account->setDebit(0);
            $mlm_dist_eshare_account->setProfit(0);
            $mlm_dist_eshare_account->setShareBalance($totalEshare);
            //$mlm_dist_eshare_account->setShareBalance($totalEsharePartition);
            $mlm_dist_eshare_account->setRemark("");
            //$mlm_dist_eshare_account->setValidSellDate($validSellDate);
            $mlm_dist_eshare_account->setStatusCode(Globals::ESHARE_ACCOUNT_STATUS_ACTIVE);
            $mlm_dist_eshare_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_dist_eshare_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_dist_eshare_account->save();

            /* ******************************
             *  Update seller
             ******************************* */
            $sellerDistId = $mlm_eshare_sell_waiting_list->getDistId();
            // eshare volume
            $mlm_dist_eshare_account = new MlmDistEshareAccount();
            $mlm_dist_eshare_account->setDistId($sellerDistId);
            $mlm_dist_eshare_account->setBuyPrice(0);
            $mlm_dist_eshare_account->setCredit(0);
            $mlm_dist_eshare_account->setDebit($totalEshare);
            $mlm_dist_eshare_account->setSellPrice($mlm_eshare_sell_waiting_list->getPrice());
            $mlm_dist_eshare_account->setProfit(0);
            $mlm_dist_eshare_account->setShareBalance(0);
            $mlm_dist_eshare_account->setRemark("waiting id:".$buyId.", dist id:".$this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_dist_eshare_account->setStatusCode(Globals::ESHARE_ACCOUNT_STATUS_ACTIVE);
            $mlm_dist_eshare_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_dist_eshare_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_dist_eshare_account->save();

            // Update Account EShare
            $accountEshareBalance = $this->getAccountBalance($sellerDistId, Globals::ACCOUNT_TYPE_ESHARE);

            $mlm_account_ledger = new MlmAccountLedger();
            $mlm_account_ledger->setDistId($sellerDistId);
            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ESHARE);
            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL);
            $mlm_account_ledger->setRemark("");
            $mlm_account_ledger->setCredit(0);
            $mlm_account_ledger->setDebit($totalEshare);
            $mlm_account_ledger->setBalance($accountEshareBalance - $totalEshare);
            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->save();

            $this->revalidateAccount($accountEshareBalance, Globals::ACCOUNT_TYPE_ESHARE);
            // ecash
            $maintenanceBalance = $buyAmount * Globals::BONUS_MAINTENANCE_PERCENTAGE;

            $distAccountECashBalance = $this->getAccountBalance($sellerDistId, Globals::ACCOUNT_TYPE_ECASH);

            $currentDate = date("Y-m-d");

            $mlm_account_ledger = new MlmAccountLedger();
            $mlm_account_ledger->setDistId($sellerDistId);
            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL_ESHARE);
            $mlm_account_ledger->setRemark("SELL ESHARE VOLUME:" . number_format($totalEshare, 2) . ", VALUE:" . $buyAmount . " (" . $currentDate . ")");
            $mlm_account_ledger->setCredit($buyAmount);
            $mlm_account_ledger->setDebit(0);
            $mlm_account_ledger->setBalance($distAccountECashBalance + $buyAmount);
            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->save();

            $maintenanceEcashAccountLedger = new MlmAccountLedger();
            $maintenanceEcashAccountLedger->setDistId($sellerDistId);
            $maintenanceEcashAccountLedger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
            $maintenanceEcashAccountLedger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);
            $maintenanceEcashAccountLedger->setRemark("MAINTENANCE BALANCE (" . number_format($buyAmount, 2) . ")");
            $maintenanceEcashAccountLedger->setCredit(0);
            $maintenanceEcashAccountLedger->setDebit($maintenanceBalance);
            $maintenanceEcashAccountLedger->setBalance($distAccountECashBalance + $buyAmount - $maintenanceBalance);
            $maintenanceEcashAccountLedger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $maintenanceEcashAccountLedger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $maintenanceEcashAccountLedger->save();

            $this->revalidateAccount($sellerDistId, Globals::ACCOUNT_TYPE_ECASH);

            // Maintenance
            $distAccountMaintenanceBalance = $this->getAccountBalance($sellerDistId, Globals::ACCOUNT_TYPE_MAINTENANCE);

            $maintenanceAccountLedger = new MlmAccountLedger();
            $maintenanceAccountLedger->setDistId($sellerDistId);
            $maintenanceAccountLedger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
            $maintenanceAccountLedger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL_ESHARE);
            $maintenanceAccountLedger->setRemark("SELL ESHARE VOLUME:" . number_format($totalEshare, 2) . ", VALUE:" . $buyAmount . " (" . $currentDate . ")");
            $maintenanceAccountLedger->setCredit($maintenanceBalance);
            $maintenanceAccountLedger->setDebit(0);
            $maintenanceAccountLedger->setBalance($distAccountMaintenanceBalance + $maintenanceBalance);
            $maintenanceAccountLedger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $maintenanceAccountLedger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $maintenanceAccountLedger->save();

            $this->revalidateAccount($sellerDistId, Globals::ACCOUNT_TYPE_MAINTENANCE);

            /* ******************************
           *  Update waiting list
           ******************************* */
            $partialQty = $waitingEshareRemaining - $totalEshare;

            $mlm_eshare_sell_waiting_list->setMatchBalance($totalEshare + $mlm_eshare_sell_waiting_list->getMatchBalance());
            if ($partialQty != 0) {
                $mlm_eshare_sell_waiting_list->setStatusCode(Globals::ESHARE_WAITING_STATUS_PARTIAL_MATCH);
            } else {
                $mlm_eshare_sell_waiting_list->setStatusCode(Globals::ESHARE_WAITING_STATUS_MATCHED);
            }
            $mlm_eshare_sell_waiting_list->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_eshare_sell_waiting_list->save();

            /* ******************************
             *  Update successful waiting list
             ******************************* */
            $mlm_eshare_sell_success_list = new MlmEshareSellSuccessList();

            $mlm_eshare_sell_success_list->setSellId($buyId);
            $mlm_eshare_sell_success_list->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_eshare_sell_success_list->setPrice($mlm_eshare_sell_waiting_list->getPrice());
            $mlm_eshare_sell_success_list->setMatchBalance($totalEshare);
            $mlm_eshare_sell_success_list->setStatusCode(Globals::STATUS_ACTIVE);
            $mlm_eshare_sell_success_list->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_eshare_sell_success_list->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));

            $mlm_eshare_sell_success_list->save();

            /* ******************************
             *  Update Account Ecash
             ******************************* */
            $c = new Criteria();
            $accountEcashBalance = 0;
            if ($maintenanceTransaction == true) {
                $accountEcashBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MAINTENANCE);
            } else {
                $accountEcashBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
            }

            $mlm_account_ledger = new MlmAccountLedger();
            if ($maintenanceTransaction == true) {
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
            } else {
                $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
            }
            $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY_ESHARE);
            $mlm_account_ledger->setRemark("");
            $mlm_account_ledger->setCredit(0);
            $mlm_account_ledger->setDebit($buyAmount);
            $mlm_account_ledger->setBalance($accountEcashBalance - $buyAmount);
            $mlm_account_ledger->setOtherSource(Globals::_YES);
            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->save();

            if ($maintenanceTransaction == true) {
                $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MAINTENANCE);
            } else {
                $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
            }

            /* ******************************
             *  Update Account EShare
             ******************************* */
            $accountEshareBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ESHARE);

            $mlm_account_ledger = new MlmAccountLedger();
            $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ESHARE);
            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY);
            $mlm_account_ledger->setRemark("");
            $mlm_account_ledger->setCredit($totalEshare);
            $mlm_account_ledger->setDebit(0);
            $mlm_account_ledger->setBalance($accountEshareBalance + $totalEshare);
            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->save();

            $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ESHARE);

            $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Buy share successfully."));

            $con->commit();

            if ($maintenanceTransaction == true) {
                return $this->redirect('/eshare/reinvestEshare');
            } else {
                return $this->redirect('/eshare/tradingCenter');
            }
        } catch (PropelException $e) {
            $con->rollback();
            throw $e;
        }
    }

    public function executeOpenTransactionListing()
    {
        Globals::SESSION_DISTID; // this is neccessary to initialial Globals class

        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = "FROM mlm_dist_eshare_account";

        /******   total records  *******/
        $sWhere = " WHERE dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $sWhere .= " AND status_code ='" . Globals::ESHARE_ACCOUNT_STATUS_ACTIVE . "'";
        $sWhere .= " AND debit = 0";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;

        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $eshareTradingService = new EShareTradingService();
        $currentEsharePrice = $eshareTradingService->fetchEsharePrice();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            $buyPrice = $resultArr['buy_price'] == null ? "0.00" : $resultArr['buy_price'];
            $totalShare = $resultArr['credit'] == null ? "0" : $resultArr['credit'];
            $validSellDate = $resultArr['valid_sell_date'] == null ? "" : $resultArr['valid_sell_date'];
            $unrealizedProfitLoss = ($currentEsharePrice - $buyPrice) * $totalShare;
            $validSell = false;

            $currentTime = strtotime(date("Y-m-d H:i:s"));
            $validSellDateTime = strtotime($validSellDate);
            /*var_dump(date("Y-m-d H:i:s"));
            var_dump("===");
            var_dump($currentTime);
            var_dump($validSellDate);
            var_dump("::");
            var_dump($validSellDateTime);*/

            if ($validSellDateTime < $currentTime || $this->getUser()->getAttribute(Globals::SESSION_DISTID) == 1) {
                $validSell = true;
            }

            $arr[] = array(
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $buyPrice,
                $totalShare,
                $unrealizedProfitLoss,
                $validSellDate,
                $validSell
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeBuyingHistoryListing()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);
        //$sColumns = str_replace("marginUsed", "(ledger.f_gram * ledger.f_original_price) AS marginUsed", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = " FROM mlm_dist_eshare_account";

        /******   total records  *******/
        $sWhere = " WHERE dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            $arr[] = array(
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $resultArr['created_on'] == null ? "" : $resultArr['created_on'],
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $resultArr['credit'] == null ? "0" : $resultArr['credit'],
                $resultArr['buy_price'] == null ? "0.00" : $resultArr['buy_price']
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeSellingWaitingListing()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);
        //$sColumns = str_replace("marginUsed", "(ledger.f_gram * ledger.f_original_price) AS marginUsed", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = " , sell.match_balance FROM mlm_eshare_sell_waiting_list sell
            LEFT JOIN mlm_distributor dist ON dist.distributor_id = sell.dist_id";

        /******   total records  *******/
        $sWhere = " WHERE sell.status_code IN ('" . Globals::ESHARE_WAITING_STATUS_ACTIVE . "','" . Globals::ESHARE_WAITING_STATUS_PARTIAL_MATCH . "')";
        $sWhere .= " AND sell.dist_id <> " . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();

            $sellingQty = $resultArr['qty'] == null ? "0" : $resultArr['qty'];
            $sellingQty = $sellingQty - $resultArr['match_balance'];

            $arr[] = array(
                $resultArr['sell_id'] == null ? "" : $resultArr['sell_id'],
                $resultArr['created_on'] == null ? "" : $resultArr['created_on'],
                $resultArr['distributor_code'] == null ? "" : $resultArr['distributor_code'],
                $resultArr['price'] == null ? "0" : $resultArr['price'],
                number_format($sellingQty, 0),
                $sellingQty
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeOwnSellingWaitingListing()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);
        //$sColumns = str_replace("marginUsed", "(ledger.f_gram * ledger.f_original_price) AS marginUsed", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = " , sell.match_balance FROM mlm_eshare_sell_waiting_list sell
            LEFT JOIN mlm_distributor dist ON dist.distributor_id = sell.dist_id";

        /******   total records  *******/
        $sWhere = " WHERE sell.dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $sWhere .= " AND sell.status_code IN ('" . Globals::ESHARE_WAITING_STATUS_ACTIVE . "','" . Globals::ESHARE_WAITING_STATUS_PARTIAL_MATCH . "')";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();

            $sellingQty = $resultArr['qty'] == null ? "0" : $resultArr['qty'];
            $sellingQty = $sellingQty - $resultArr['match_balance'];

            $arr[] = array(
                $resultArr['sell_id'] == null ? "" : $resultArr['sell_id'],
                $resultArr['created_on'] == null ? "" : $resultArr['created_on'],
                $resultArr['distributor_code'] == null ? "" : $resultArr['distributor_code'],
                $resultArr['price'] == null ? "0" : $resultArr['price'],
                number_format($sellingQty, 0),
                $resultArr['status_code'] == null ? "" : $resultArr['status_code'],
                $resultArr['status_code'] == null ? "" : $resultArr['status_code']
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeSellingHistoryListing()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = " FROM mlm_dist_eshare_account";

        /******   total records  *******/
        $sWhere = " WHERE dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $sWhere .= " AND debit <> 0 ";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            //var_dump(round(resultArr['marginUsed']));
            //var_dump("==>".round($resultArr['marginUsed'], 2, PHP_ROUND_HALF_UP));
            $arr[] = array(
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $resultArr['created_on'] == null ? "" : $resultArr['created_on'],
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $resultArr['debit'] == null ? "0" : $resultArr['debit'],
                $resultArr['sell_price'] == null ? "0.00" : $resultArr['sell_price'],
                $resultArr['profit'] == null ? "0.00" : $resultArr['profit']
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeSellingHistoryListing_bak()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = " FROM mlm_dist_eshare_account";

        /******   total records  *******/
        $sWhere = " WHERE dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $sWhere .= " AND debit <> 0 AND status_code = '" . Globals::ESHARE_ACCOUNT_STATUS_COMPLETE . "'";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            //var_dump(round(resultArr['marginUsed']));
            //var_dump("==>".round($resultArr['marginUsed'], 2, PHP_ROUND_HALF_UP));
            $arr[] = array(
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $resultArr['sell_date'] == null ? "" : $resultArr['sell_date'],
                $resultArr['eshare_id'] == null ? "" : $resultArr['eshare_id'],
                $resultArr['debit'] == null ? "0" : $resultArr['debit'],
                $resultArr['sell_price'] == null ? "0.00" : $resultArr['sell_price'],
                $resultArr['profit'] == null ? "0.00" : $resultArr['profit']
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    /* *************************************************************************
     *      FUNCTION
     ************************************************************************** */

    function revalidateAccount($distributorId, $accountType)
    {
        $balance = $this->getAccountBalance($distributorId, $accountType);

        $c = new Criteria();
        $c->add(MlmAccountPeer::ACCOUNT_TYPE, $accountType);
        $c->add(MlmAccountPeer::DIST_ID, $distributorId);
        $tbl_account = MlmAccountPeer::doSelectOne($c);

        if (!$tbl_account) {
            $tbl_account = new MlmAccount();
            $tbl_account->setDistId($distributorId);
            $tbl_account->setAccountType($accountType);
        }

        $tbl_account->setBalance($balance);
        $tbl_account->save();
    }

    function getAccountBalance($distributorId, $accountType)
    {
        $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_account_ledger WHERE dist_id = " . $distributorId . " AND account_type = '" . $accountType . "'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function getTotalRecords($sql)
    {
        $query = "SELECT COUNT(*) AS _TOTAL " . $sql;
        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $count = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_TOTAL"] != null) {
                $count = $arr["_TOTAL"];
            } else {
                $count = 0;
            }
        }
        return $count;
    }

    function getPaperShareQuantity($distributorId)
    {
        $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_dist_eshare_account
                    WHERE dist_id = " . $distributorId;

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $paperShareQuantity = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                $paperShareQuantity = $arr["SUB_TOTAL"];
            }
        }
        return $paperShareQuantity;
    }

    function getPaperShareQuantityInWaitingList($distributorId)
    {
        $query = "SELECT SUM(qty) AS SUB_TOTAL FROM mlm_eshare_sell_waiting_list
                    WHERE dist_id = " . $distributorId
                 . " AND status_code IN ('" . Globals::ESHARE_WAITING_STATUS_ACTIVE . "','" . Globals::ESHARE_WAITING_STATUS_PARTIAL_MATCH . "')";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $paperShareQuantity = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                $paperShareQuantity = $arr["SUB_TOTAL"];
            }
        }
        return $paperShareQuantity;
    }
}
